﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Foothill.Bus;
using Foothill.Bus.Result;
using Foothill.Bus.Node;
using Foothill.Persistent.Command;
using Foothill.Core.FieldMapping;
using GGFW.Entity.DocumentsManage;
using GGFW.DataAccess.Constants;

namespace GGFW.DataAccess.DocumentsManage
{
    public class ProductOrderDao : BaseDao
    {
        #region 分页获取生产单信息数据列表
        /// <summary>
        /// 分页获取生产单信息数据列表
        /// </summary>
        /// <param name="PageSize">页面大小</param>
        /// <param name="PageIndex">当前页数</param>
        /// <param name="queryEntity">查询条件实体</param>
        /// <returns>业务代码数据列表</returns>
        public List<productgrid> QueryByPageProductGrid(int PageSize, int PageIndex, productgrid productgridEntity, out int totalCount)
        {
            string querySql = this.getQuerySqlerProductGrid(productgridEntity);

            //结果总数
            totalCount = this.GetQueryCount(querySql);
            querySql += this.getOrderSqlProductGrid(productgridEntity);
            //设置查询企业基本信息总线
            NativeSqlBus queryBus = new NativeSqlBus("productgrid", querySql);

            //执行查询业务代码命令
            QueryNativeSqlCommand queryCommand = new QueryNativeSqlCommand(queryBus);
            ResultQueryNativeSql result = queryCommand.executePage(PageIndex, PageSize) as ResultQueryNativeSql;


            //执行结果为空
            if (result == null)
            {
                throw new Exception(GGFW.DataAccess.Constants.Common.UNKNOW_DB_ERROR);
            }

            //执行命令不成功
            if (!result.isSuccess())
            {
                throw new Exception(result.getMessage());
            }

            //是否存在记录
            if (result.Records != null && result.Records.Count > 0)
            {
                List<productgrid> dataList = new List<productgrid>();
                AbstractResultBus.CopyProperties<productgrid>(dataList, result.Records, null);
                MappingFieldEvaluator.ProcessList<productgrid>(dataList);

                return dataList;
            }
            return null;
        }
        /// <summary>
        /// 获得查询Sql
        /// </summary>
        /// <param name="queryEntity">查询条件实体</param>
        /// <returns>查询Sql</returns>
        private string getQuerySqlerProductGrid(productgrid productgridEntity)
        {
            StringBuilder sqlText = new StringBuilder();
            sqlText.Append("select * from productgrid where 1=1");
            //是否查询生产单编号
            if (productgridEntity.pro_id != 0)
            {
                sqlText.Append(string.Format(" and pro_id = '{0}' ", productgridEntity.pro_id));
            }
            return sqlText.ToString();
        }
        /// <summary>
        /// 获得排序语句
        /// </summary>
        /// <param name="queryEntity">查询条件</param>
        /// <returns>排序语句</returns>
        private string getOrderSqlProductGrid(productgrid productgridEntity)
        {
            string OrderField = "";
            if (!string.IsNullOrEmpty(productgridEntity.OrderField))
            {
                OrderField = productgridEntity.OrderField;
            }
            return SqlUtil.GetOrderSql(OrderField, productgridEntity.OrderDirection, "pro_id", "asc");
        }
        #endregion

        #region 保存产品主表信息
        /// <summary>
        /// 保存产品主表信息
        /// </summary>
        /// <param name="model">产品实体</param>
        /// <returns>产品ID</returns>
        public int SaveProduct(string productFileds)
        {
            int pro_id = 0;
            string sql = "Insert into product({0}) values({1});select @@IDENTITY";
            string[] filed_NameValue = productFileds.Split('ξ');
            string filed_Name = filed_NameValue[0];
            string filed_Value = filed_NameValue[1];
            sql = string.Format(sql, filed_Name, filed_Value);
            //设置查询企业基本信息总线
            NativeSqlBus queryBus = new NativeSqlBus("product", sql);

            //执行查询业务代码命令
            QueryNativeSqlCommand queryCommand = new QueryNativeSqlCommand(queryBus);
            ResultQueryNativeSql result = queryCommand.execute() as ResultQueryNativeSql;

            //执行结果为空
            if (result == null)
            {
                throw new Exception(Common.UNKNOW_DB_ERROR);
            }

            //执行命令不成功
            if (!result.isSuccess())
            {
                throw new Exception(result.getMessage());
            }

            //是否存在记录
            if (result.Records != null && result.Records.Count > 0)
            {
                pro_id = Convert.ToInt32(result.Records[0].Fields[0].FieldValue);
            }

            return pro_id;
        }
        #endregion

        #region 保存产品附加表信息
        /// <summary>
        /// 保存产品附加表信息
        /// </summary>
        /// <param name="productFileds"></param>
        /// <returns></returns>
        public void SaveProductAdd(string productaddFileds, int pro_id)
        {
            string sql = "Insert into productadd({0}) values({1})";
            string[] filed_NameValue = productaddFileds.Split('ξ');
            string filed_Name = filed_NameValue[0];
            string filed_Value = filed_NameValue[1].Replace("∮pro_id∮", pro_id.ToString());
            sql = string.Format(sql, filed_Name, filed_Value);
            //设置查询企业基本信息总线
            NativeSqlBus queryBus = new NativeSqlBus("productadd", sql);

            //执行查询业务代码命令
            QueryNativeSqlCommand queryCommand = new QueryNativeSqlCommand(queryBus);
            ResultQueryNativeSql result = queryCommand.execute() as ResultQueryNativeSql;

            //执行结果为空
            if (result == null)
            {
                throw new Exception(Common.UNKNOW_DB_ERROR);
            }

            //执行命令不成功
            if (!result.isSuccess())
            {
                throw new Exception(result.getMessage());
            }
        }
        #endregion

        #region 保存帽款表信息
        /// <summary>
        /// 保存帽款表信息
        /// </summary>
        /// <param name="maokuanFileds"></param>
        /// <returns></returns>
        public void SaveMaoKuan(string maokuanFileds, int pro_id)
        {
            string sql = "Insert into maokuan({0}) values({1})";
            string[] filed_NameValue = maokuanFileds.Split('ξ');
            string filed_Name = filed_NameValue[0];
            string filed_Value = filed_NameValue[1].Replace("∮pro_id∮", pro_id.ToString());
            sql = string.Format(sql, filed_Name, filed_Value);
            //设置查询企业基本信息总线
            NativeSqlBus queryBus = new NativeSqlBus("maokuan", sql);

            //执行查询业务代码命令
            QueryNativeSqlCommand queryCommand = new QueryNativeSqlCommand(queryBus);
            ResultQueryNativeSql result = queryCommand.execute() as ResultQueryNativeSql;

            //执行结果为空
            if (result == null)
            {
                throw new Exception(Common.UNKNOW_DB_ERROR);
            }

            //执行命令不成功
            if (!result.isSuccess())
            {
                throw new Exception(result.getMessage());
            }
        }
        #endregion

        #region 保存包装表信息
        /// <summary>
        /// 保存包装表信息
        /// </summary>
        /// <param name="baozhuangFileds"></param>
        /// <returns></returns>
        public void SaveBaoZhuang(string baozhuangFileds, int pro_id)
        {
            string sql = "Insert into baozhuang({0}) values({1})";
            string[] filed_NameValue = baozhuangFileds.Split('ξ');
            string filed_Name = filed_NameValue[0];
            string filed_Value = filed_NameValue[1].Replace("∮pro_id∮", pro_id.ToString());
            sql = string.Format(sql, filed_Name, filed_Value);
            //设置查询企业基本信息总线
            NativeSqlBus queryBus = new NativeSqlBus("baozhuang", sql);

            //执行查询业务代码命令
            QueryNativeSqlCommand queryCommand = new QueryNativeSqlCommand(queryBus);
            ResultQueryNativeSql result = queryCommand.execute() as ResultQueryNativeSql;

            //执行结果为空
            if (result == null)
            {
                throw new Exception(Common.UNKNOW_DB_ERROR);
            }

            //执行命令不成功
            if (!result.isSuccess())
            {
                throw new Exception(result.getMessage());
            }
        }
        #endregion

        #region 保存产品Grid表信息
        /// <summary>
        /// 保存产品Grid表信息
        /// </summary>
        /// <param name="productFileds"></param>
        /// <returns></returns>
        public void SaveProductGrid(string productgridFileds, int pro_id)
        {
            StringBuilder sb = new StringBuilder();
            string[] filed_NameValue = null;
            string filed_Name = null;
            string filed_Value = null;
            string sql = "Insert into productgrid({0}) values({1})";
            string[] arr_fileds = productgridFileds.Split('₃');
            for (int i = 0; i < arr_fileds.Length; i++)
            {
                filed_NameValue = arr_fileds[i].Split('ξ');
                filed_Name = filed_NameValue[0];
                filed_Value = filed_NameValue[1].Replace("∮pro_id∮", pro_id.ToString());
                sb.Append(string.Format(sql, filed_Name, filed_Value));
                if (i < arr_fileds.Length - 1)
                    sb.Append(";");
            }

            //设置查询企业基本信息总线
            NativeSqlBus queryBus = new NativeSqlBus("productgrid", sb.ToString());

            //执行查询业务代码命令
            QueryNativeSqlCommand queryCommand = new QueryNativeSqlCommand(queryBus);
            ResultQueryNativeSql result = queryCommand.execute() as ResultQueryNativeSql;

            //执行结果为空
            if (result == null)
            {
                throw new Exception(Common.UNKNOW_DB_ERROR);
            }

            //执行命令不成功
            if (!result.isSuccess())
            {
                throw new Exception(result.getMessage());
            }
        }
        #endregion

        #region 获取生产单列表数据
        /// <summary>
        /// 获取生产单列表数据
        /// </summary>
        /// <param name="PageSize"></param>
        /// <param name="PageIndex"></param>
        /// <param name="queryEntity"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public List<product> SearchProductOrderList(int PageSize, int PageIndex, product productqueryEntity, out int totalCount)
        {
            string querySql = this.getQuerySqlerProductList(productqueryEntity);

            //结果总数
            totalCount = this.GetQueryCount(querySql);

            //querySql = querySql + " ORDER BY State DESC, RecordCreateDate DESC";

            querySql += getOrderSqlproduct(productqueryEntity);

            //设置查询企业基本信息总线
            NativeSqlBus queryBus = new NativeSqlBus("product", querySql);

            //执行查询业务代码命令
            QueryNativeSqlCommand queryCommand = new QueryNativeSqlCommand(queryBus);
            ResultQueryNativeSql result = queryCommand.executePage(PageIndex, PageSize) as ResultQueryNativeSql;

            //执行结果为空
            if (result == null)
            {
                throw new Exception(GGFW.DataAccess.Constants.Common.UNKNOW_DB_ERROR);
            }

            //执行命令不成功
            if (!result.isSuccess())
            {
                throw new Exception(result.getMessage());
            }

            //是否存在记录
            if (result.Records != null && result.Records.Count > 0)
            {
                List<product> list = new List<product>();
                AbstractResultBus.CopyProperties<product>(list, result.Records, null);
                return list;
            }
            return null;
        }
        /// <summary>
        /// 获得查询Sql
        /// </summary>
        /// <param name="queryEntity">查询条件实体</param>
        /// <returns>查询Sql</returns>
        private string getQuerySqlerProductList(product productqueryEntity)
        {
            StringBuilder sqlText = new StringBuilder();
            sqlText.Append("select * from product where isdeleted=0");
            //是否查询生产单编号
            if (productqueryEntity.pro_id != 0)
            {
                sqlText.Append(string.Format(" and pro_id = '{0}' ", productqueryEntity.pro_id));
            }
            //是否查询生产单编号
            if (!string.IsNullOrEmpty(productqueryEntity.code))
            {
                sqlText.Append(string.Format(" and code like '%{0}%' ", productqueryEntity.code));
            }
            //是否查询生产单款名
            if (!string.IsNullOrEmpty(productqueryEntity.pro_name))
            {
                sqlText.Append(string.Format(" and pro_name like '%{0}%' ", productqueryEntity.pro_name));
            }
            return sqlText.ToString();
        }
        /// <summary>
        /// 获得排序语句
        /// </summary>
        /// <param name="queryEntity">查询条件</param>
        /// <returns>排序语句</returns>
        private string getOrderSqlproduct(product productqueryEntity)
        {
            string OrderField = "";
            if (!string.IsNullOrEmpty(productqueryEntity.OrderField))
            {
                OrderField = "product." + productqueryEntity.OrderField;
            }
            return SqlUtil.GetOrderSql(OrderField, productqueryEntity.OrderDirection, "product.pro_id", "desc");
        }
        #endregion

        #region 删除产品信息
        /// <summary>
        /// 删除产品信息
        /// 并不真正删除，只是将isdeleted置为1
        /// </summary>
        /// <param name="pro_id"></param>
        public void DelProductByProID(int pro_id)
        {
            string sql = string.Format("update product set isdeleted=1 where pro_id='{0}'", pro_id);
            //设置查询企业基本信息总线
            NativeSqlBus queryBus = new NativeSqlBus("product", sql);


            //执行查询业务代码命令
            QueryNativeSqlCommand queryCommand = new QueryNativeSqlCommand(queryBus);
            ResultQueryNativeSql result = queryCommand.execute() as ResultQueryNativeSql;

            //执行结果为空
            if (result == null)
            {
                throw new Exception(GGFW.DataAccess.Constants.Common.UNKNOW_DB_ERROR);
            }
            //执行命令不成功
            if (!result.isSuccess())
            {
                throw new Exception(result.getMessage());
            }
        }
        #endregion

        #region 获取产品附加信息（修改网页）
        /// <summary>
        /// 获取产品附加信息（修改网页）
        /// </summary>
        /// <param name="ProID"></param>
        /// <returns></returns>
        public List<v_productinfo> LoadProductInfo(string ProID)
        {
            string sql = null;

            sql = string.Format("select * from v_productinfo where pro_id={0}", ProID);

            //设置查询企业基本信息总线
            NativeSqlBus queryBus = new NativeSqlBus("v_productinfo", sql);


            //执行查询业务代码命令
            QueryNativeSqlCommand queryCommand = new QueryNativeSqlCommand(queryBus);
            ResultQueryNativeSql result = queryCommand.execute() as ResultQueryNativeSql;

            //执行结果为空
            if (result == null)
            {
                throw new Exception(Common.UNKNOW_DB_ERROR);
            }

            //执行命令不成功
            if (!result.isSuccess())
            {
                throw new Exception(result.getMessage());
            }
            //是否存在记录
            if (result.Records != null && result.Records.Count > 0)
            {
                List<v_productinfo> dataList = new List<v_productinfo>();
                AbstractResultBus.CopyProperties<v_productinfo>(dataList, result.Records, null);
                MappingFieldEvaluator.ProcessList<v_productinfo>(dataList);

                return dataList;
            }
            return null;
        }
        #endregion

        #region 更新产品附加信息
        /// <summary>
        /// 更新产品附加信息
        /// </summary>
        /// <param name="ProID"></param>
        /// <param name="TabName"></param>
        /// <param name="FiledName"></param>
        /// <param name="FiledValue"></param>
        /// <returns></returns>
        public bool UpdateProductInfo(string ProID, string TabName, string FiledName, string FiledValue)
        {
            string sql = null;

            if (FindProductInfo(ProID, TabName) == true)
                sql = string.Format("update {0} set {1}='{2}' where pro_id={3}", TabName, FiledName, FiledValue, ProID);
            else
                sql = string.Format("insert into {0} (pro_id, {1}) values({2}, '{3}')", TabName, FiledName, ProID, FiledValue);

            //设置查询企业基本信息总线
            NativeSqlBus queryBus = new NativeSqlBus(TabName, sql);


            //执行查询业务代码命令
            QueryNativeSqlCommand queryCommand = new QueryNativeSqlCommand(queryBus);
            ResultQueryNativeSql result = queryCommand.execute() as ResultQueryNativeSql;

            //执行结果为空
            if (result == null)
            {
                throw new Exception(Common.UNKNOW_DB_ERROR);
            }

            //执行命令不成功
            if (!result.isSuccess())
            {
                throw new Exception(result.getMessage());
            }

            return true;
        }

        /// <summary>
        /// 查询产品信息
        /// </summary>
        /// <param name="ProID"></param>
        /// <param name="TabName"></param>
        /// <returns></returns>
        public bool FindProductInfo(string ProID, string TabName)
        {
            string sql = null;

            sql = string.Format("select * from {0} where pro_id={1}", TabName, ProID);

            //设置查询企业基本信息总线
            NativeSqlBus queryBus = new NativeSqlBus(TabName, sql);


            //执行查询业务代码命令
            QueryNativeSqlCommand queryCommand = new QueryNativeSqlCommand(queryBus);
            ResultQueryNativeSql result = queryCommand.execute() as ResultQueryNativeSql;

            //执行结果为空
            if (result == null)
            {
                throw new Exception(Common.UNKNOW_DB_ERROR);
            }

            //执行命令不成功
            if (!result.isSuccess())
            {
                throw new Exception(result.getMessage());
            }
            //是否存在记录
            if (result.Records != null && result.Records.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region 更新产品GRID
        /// <summary>
        ///  更新产品GRID
        /// </summary>
        /// <param name="PgID"></param>
        /// <param name="ProID"></param>
        /// <param name="FieldName"></param>
        /// <param name="FieldValue"></param>
        /// <returns></returns>
        public productgrid UpdateProductGridField(string PgID, string ProID, string FieldName, string FieldValue)
        {
            string sql = null;
            if (int.Parse(PgID) > 0)
            {
                sql = string.Format("update productgrid set {0}='{1}' where pg_id='{2}'", FieldName, FieldValue, PgID);
            }
            else
            {
                sql = string.Format("insert into productgrid (pro_id, {0}) values({1}, {2}); select @@IDENTITY", FieldName, ProID, FieldValue);
            }

            //设置查询企业基本信息总线
            NativeSqlBus queryBus = new NativeSqlBus("productgrid", sql);


            //执行查询业务代码命令
            QueryNativeSqlCommand queryCommand = new QueryNativeSqlCommand(queryBus);
            ResultQueryNativeSql result = queryCommand.execute() as ResultQueryNativeSql;

            //执行结果为空
            if (result == null)
            {
                throw new Exception(Common.UNKNOW_DB_ERROR);
            }

            //执行命令不成功
            if (!result.isSuccess())
            {
                throw new Exception(result.getMessage());
            }
            //是否存在记录
            if (result.Records != null && result.Records.Count > 0)
            {
                productgrid pg = new productgrid();
                pg.pg_id = Convert.ToInt32(result.Records[0].Fields[0].FieldValue);
                return pg;
            }
            return null;
        }
        #endregion

        #region 获取产品信息
        /// <summary>
        /// 获取产品信息
        /// </summary>
        /// <param name="taskname"></param>
        /// <param name="starttime"></param>
        /// <param name="endtime"></param>
        /// <returns></returns>
        public List<product> GetProductOrder(int pageSize, int pageNo, string queryWord, string type, out int totalCount)
        {
            string condition = "where isdeleted!=1";
            if (type.ToLower().Equals("pro_name"))
            {
                if (!string.IsNullOrEmpty(queryWord))
                    condition += string.Format(" and pro_name like '{0}%'", queryWord);
            }
            else
            {
                if (!string.IsNullOrEmpty(queryWord))
                    condition += string.Format(" and code like '{0}%'", queryWord);
            }

            string sql = "select * from product " + condition + " ";

            totalCount = GetQueryCount(sql);

            //设置查询企业基本信息总线
            NativeSqlBus queryBus = new NativeSqlBus("product", sql);

            //执行查询业务代码命令
            QueryNativeSqlCommand queryCommand = new QueryNativeSqlCommand(queryBus);
            ResultQueryNativeSql result = queryCommand.executePage(pageNo, pageSize) as ResultQueryNativeSql;

            //执行结果为空
            if (result == null)
            {
                throw new Exception(GGFW.DataAccess.Constants.Common.UNKNOW_DB_ERROR);
            }

            //执行命令不成功
            if (!result.isSuccess())
            {
                throw new Exception(result.getMessage());
            }

            //是否存在记录
            if (result.Records != null && result.Records.Count > 0)
            {
                List<product> dataList = new List<product>();
                AbstractResultBus.CopyProperties<product>(dataList, result.Records, null);
                MappingFieldEvaluator.ProcessList<product>(dataList);
                return dataList;
            }
            return null;
        }
        #endregion
    }
}
